package com.apes.fn.ssm.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.fn.ssm.model.FittingMessage;
import com.apes.framework.query.ApesDao;
import com.apes.scm.masterdata.product.model.Brand;
import org.springframework.stereotype.Component;

import java.util.Collection;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * @description: 动态条件查询优先级
 * @author: wsl
 * @time: 2020/11/30 16:21
 */
@Component
public class BrandPriorityDao extends ApesDao {
    /**
     * 批量查询优先级配置
     * @param param
     * @return
     */
    public JSONArray queryBrandPriority(JSONObject param) {
        JSONObject request = new JSONObject();
        StringBuffer sqlBuffer = new StringBuffer(" SELECT B.ID,B.VERSION,B.LOCATION_ID, B.FITTING_NAME_ID, B.BRAND_ID, B.PRIORITY, B.VALID,\n" +
                "        C.NAME LOCATION_NAME,F.NAME FITTING_NAME,D.NAME BRAND_NAME, E.DESCRIPTION PRIORITY_NAME\n" +
                "      FROM MAS_BRAND_PRIORITY B, MAS_LOCATION C, MAS_FITTING_MESSAGE F, MAS_BRAND D, MAS_ENUMERATION E\n" +
                "      WHERE B.LOCATION_ID = C.ID AND B.FITTING_NAME_ID = F.ID\n" +
                "      AND B.BRAND_ID = D.ID AND B.PRIORITY = E.ID");

        if (Objects.nonNull(param.getJSONObject("location")) && param.getJSONObject("location").size() >0) {
            JSONObject locationJSON = param.getJSONObject("location");
            sqlBuffer.append(" AND B.LOCATION_ID = '" + locationJSON.getString("id") + "'");
        }if (Objects.nonNull(param.getJSONObject("fittingName")) && param.getJSONObject("fittingName").size() >0) {
            JSONObject fittingNameJSON = param.getJSONObject("fittingName");
            sqlBuffer.append(" AND B.FITTING_NAME_ID = " + fittingNameJSON.getString("id"));
        }if (Objects.nonNull(param.getJSONObject("brand")) && param.getJSONObject("brand").size() >0) {
            JSONObject brandJSON = param.getJSONObject("brand");
            sqlBuffer.append(" AND B.BRAND_ID = " + brandJSON.getString("id"));
        }if (Objects.nonNull(param.get("valid"))) {
            Boolean b = Boolean.valueOf(param.get("valid").toString());
            String array = b ? "1" : "0";
            sqlBuffer.append(" AND B.VALID = " + array);
        }
        String sql = sqlBuffer.toString();
        return toJSONArray(find(sql, request));
    }



    /**
     * 查询商品个数
     * @param fittingMessage
     * @return
     */
    public List<Map> findBrandCount(FittingMessage fittingMessage){
        StringBuffer sqlBuffer = new StringBuffer("SELECT COUNT(*) AS COUNT FROM MAS_PRODUCT A WHERE A.CATEGORY_ID IN (SELECT B.CATEGORY_ID FROM MAS_SUB_CATEGORY B WHERE B.FITTING_MESSAGE_ID = ");
        sqlBuffer.append(fittingMessage.getId() + ')');
        if (Objects.nonNull(fittingMessage.getBrand()) && fittingMessage.getBrand().size() > 0){
            sqlBuffer.append(" AND A.BRAND_ID IN (");
            getQueryString(fittingMessage.getBrand(),sqlBuffer);
        }
        if (Objects.nonNull(fittingMessage.getProduct()) && !"[]".equals(fittingMessage.getProduct())){
            sqlBuffer.append(" AND A.STATE IN (");
            getQueryString(sqlBuffer,JSONArray.parseArray(fittingMessage.getProduct()));
        }
        return find(sqlBuffer.toString());
    }

    /**
     * 查询商品个数
     * @param fittingMessage
     * @return
     */
    public List<Map> findBrandCount(FittingMessage fittingMessage, List<String> brandId){
        StringBuffer sqlBuffer = new StringBuffer("SELECT COUNT(*) AS COUNT FROM MAS_PRODUCT A WHERE A.CATEGORY_ID IN " +
                "(SELECT B.CATEGORY_ID FROM MAS_SUB_CATEGORY B WHERE B.FITTING_MESSAGE_ID = ");
        sqlBuffer.append(fittingMessage.getId() + ')');
        if (brandId.size() > 0 ){
            sqlBuffer.append(" AND A.BRAND_ID IN (" + brandId.toString().substring(1,brandId.toString().length()-1) +")");
        }

        if (Objects.nonNull(fittingMessage.getProduct()) && !"[]".equals(fittingMessage.getProduct())){
            sqlBuffer.append(" AND A.STATE IN (");
            getQueryString(sqlBuffer,JSONArray.parseArray(fittingMessage.getProduct()));
        }
        return find(sqlBuffer.toString());
    }

    /**
     *根据仓库和测算配件查询品牌优先级
     * @param locationArray
     * @param fittingMessageItems
     * @return
     */
    public List<Map> findByLocationId(JSONArray locationArray, JSONArray fittingMessageItems){
        StringBuffer sqlBuffer = new StringBuffer("SELECT A.ID,A.LOCATION_ID,A.FITTING_NAME_ID," +
                "(SELECT B.NAME FROM MAS_LOCATION B WHERE A.LOCATION_ID = B.ID) LOCATION_NAME,\n" +
                "(SELECT C.NAME FROM MAS_FITTING_MESSAGE C WHERE A.FITTING_NAME_ID = C.ID) FITTINGMESSAGE_NAME,\n" +
                "(SELECT D.NAME FROM MAS_BRAND D WHERE A.BRAND_ID = D.ID) BRAND_NAME,\n" +
                "(SELECT E.DESCRIPTION FROM MAS_ENUMERATION E WHERE A.PRIORITY = E.ID) PRIORITY,\n" +
                "A.BRAND_ID FROM MAS_BRAND_PRIORITY A WHERE A.LOCATION_ID IN (");
        getQueryString(sqlBuffer, locationArray);
        sqlBuffer.append(" AND A.FITTING_NAME_ID IN(");
        getQueryString(sqlBuffer, fittingMessageItems);
        sqlBuffer.append(" AND A.VALID = '1'");
        return find(sqlBuffer.toString());
    }

    /**
     * 获取查选条件字符串(in)
     * @param sql sql 语句
     * @param array 条件数组
     * @return 字符串
     */
    private StringBuffer getQueryString(List<Brand> array, StringBuffer sql) {
        for (int i = 0; i < array.size(); i++) {
            if (i == 0) {
                sql.append("'").append(array.get(i).getId()).append("'");
            } else {
                sql.append(",'").append(array.get(i).getId()).append("'");
            }
        }
        sql.append(")");

        return sql;
    }

    /**
     * 获取查选条件字符串
     * @param sql sql 语句
     * @param array 条件数组
     * @return 字符串
     */
    private StringBuffer getQueryString(StringBuffer sql, JSONArray array) {
        for (int i = 0; i < array.size(); i++) {
            JSONObject json = array.getJSONObject(i);
            if (i == 0) {
                sql.append("'").append(json.getString("id")).append("'");
            } else {
                sql.append(",'").append(json.getString("id")).append("'");
            }
        }
        sql.append(")");

        return sql;
    }


    /**
     * 获取查选条件字符串 - 生成商品使用
     * @param array 条件数组
     * @return 字符串
     */
    public String getQueryString(Collection array) {
        String returnValue = "";
        for (int i = 0; i < array.size(); i++) {
            String condiValue = (array instanceof JSONArray)?((JSONArray) array).getJSONObject(i).getString("id"):((Brand)((List)array).get(i)).getId();
            if (i == 0) {
                returnValue += "" + condiValue + "";
            } else {
                returnValue += "," + condiValue + "";
            }
        }
        return returnValue;
    }
}
